﻿using System;

using System.Collections.Generic;
using System.Linq;
using System.Text;
//---------------------------------------------------------------
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;//针对输入输出流操作（对文本文件操作）

namespace DAL
{
    /// <summary>
    /// 通用数据访问类
    /// 注意：如果将该类用于WEB开发的时候，请将错误信息写入Access数据库，避免多个用户读写文件的并发问题。
    /// </summary>
    public class SQLHelperForRightDB
    {
        private static string connString = ConfigurationManager.ConnectionStrings["rightDBString"].ToString();

        #region 执行格式化的SQL语句

        /// <summary>
        /// 执行增删改操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int Update(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //将错误信息写入文本文件
                WriteErrorLog("执行Update(string sql)方法时发生错误，具体信息：" + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 执行单一结果查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetSingleResult(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                //将错误信息写入文本文件
                WriteErrorLog("执行GetSingleResult(string sql)方法时发生错误，具体信息：" + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 返回只读数据集的查询
        /// </summary>
        /// <param name="sql">可以是一条或多条SQL语句（多条SQL语句用分号分隔）</param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                //将错误信息写入文本文件
                WriteErrorLog("执行GetReader(string sql)方法时发生错误，具体信息：" + ex.Message);
                conn.Close();//如果发生错误则需要关闭连接
                throw ex;
            }
        }
      
        #endregion

        #region 带参数的SQL语句
        /// <summary>
        /// 使用带参数的SQL语句执行更新操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int Update(string sql, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //将错误信息写入文本文件
                WriteErrorLog("执行(string sql, SqlParameter[] param)方法时发生错误，具体信息：" + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        public static SqlDataReader GetReader(string sql, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                //将错误信息写入文本文件
                WriteErrorLog("执行public static SqlDataReader GetReader(string sql, SqlParameter[] param)方法时发生错误，具体信息：" + ex.Message);
                conn.Close();//如果发生错误则需要关闭连接
                throw ex;
            }
        }
        public static object GetSingleResult(string sql, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                //将错误信息写入文本文件
                WriteErrorLog("执行public static object GetSingleResult(string sql, SqlParameter[] param)方法时发生错误，具体信息：" + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region 调用存储过程

        /// <summary>
        /// 使用存储过程执行更新操作(参数可以为Null)
        /// </summary>
        /// <param name="storeProcedureName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int UpdateByProcedure(string storeProcedureName, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storeProcedureName;
            try
            {
                conn.Open();
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //将错误信息写入文本文件
                WriteErrorLog("执行UpdateByProcedure(string storeProcedureName, SqlParameter[] param)方法时发生错误，具体信息："
                    + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        ///  使用存储过程执行多结果查询(参数可以为Null)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader GetReaderByProcedure(string procedureName, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procedureName;
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                SqlDataReader objReader =
                    cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return objReader;
            }
            catch (Exception ex)
            {
                conn.Close();
                WriteErrorLog("执行GetReaderByProcedure(string procedureName, SqlParameter[] param)方法时发生错误，具体信息："
                  + ex.Message);
                throw ex;
            }
        }
       
        #endregion

        #region 获取数据库服务器的时间、将错误信息写入日志

        /// <summary>
        /// 获取数据库服务器的时间
        /// </summary>
        /// <returns></returns>
        public static DateTime GetDBServerTime()
        {
            string sql = "select getdate()";
            return Convert.ToDateTime(GetSingleResult(sql));
        }
        //将错误信息写入日志
        private static void WriteErrorLog(string msg)
        {
            //创建文件流（如果文件不存在，则创建新日志文件，如果文件存在，则在文件末尾追加内容）
            FileStream fs = new FileStream("RightManager.log", FileMode.Append);
            //创建写入器
            StreamWriter sw = new StreamWriter(fs);
            //写入日志信息
            sw.WriteLine("【" + DateTime.Now.ToString() + "】错误信息：" + msg);
            //关闭写入器
            sw.Close();
            //关闭文件流
            fs.Close();
        }

        #endregion
    }
}
